﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;
using BeiraBaga.Output;

namespace BeiraBaga
{
    public class Controller
    {
        #region Members

        private const string QueryTeamTotals = @"
            select  team,
                    sum(total_hours) as total_hours,
                    sum(total_kgs) as total_kgs
            from    ( 
                    select  frn.tipoterceiro as team,
                            0 as total_hours, 
                            -sum(lin.quantidade) as total_kgs
                    from    fornecedores frn, cabeccompras as doc, linhascompras as lin
                    where   doc.id = lin.idcabeccompras and
                            doc.entidade = frn.fornecedor and
                            frn.cdu_funcionario = 1 and
                            doc.datadoc between @Start and @End and
                            doc.tipodoc = 'VFA' and
					        lin.unidade = 'KG'
                    group by frn.tipoterceiro
                    union all
                    select  frn.tipoterceiro as team,
                            sum(hor.cdu_numerohorasdia) as total_hours, 
                            0 as total_kgs
                    from    tdu_horarios as hor, fornecedores frn
                    where   hor.cdu_data >= @Start and 
                            hor.cdu_data <= @End and
                            frn.fornecedor = hor.cdu_funcionario
                    group by frn.tipoterceiro
                    ) t
            group by team";

        private const string QueryTeamEmployees = @"
            select  sum(total_hours) as hours,
                    sum(total_kgs) as kgs,
					max(employee) as employee,
					max(employee_name) as employee_name,
                    max(team) as team,
					max(cost) as cost
            from    ( 
                    select  0 as total_hours, 
                            -sum(lin.quantidade) as total_kgs,
							max(frn.cdu_custodahora) as cost,
							frn.fornecedor as employee,
							max(frn.nome) as employee_name,
                            max(tea.descricao) as team
                    from    fornecedores frn, cabeccompras as doc, linhascompras as lin, tipoterceiros tea
                    where   doc.id = lin.idcabeccompras and
                            doc.entidade = frn.fornecedor and
                            frn.cdu_funcionario = 1 and
                            doc.datadoc between @Start and @End and
                            doc.tipodoc = 'VFA' and
					        lin.unidade = 'KG' and
                            tea.tipoterceiro = frn.tipoterceiro and
                            tea.tipoterceiro = @Team
                    group by frn.fornecedor
                    union all
                    select  sum(hor.cdu_numerohorasdia) as total_hours, 
                            0 as total_kgs,
							max(frn.cdu_custodahora) as cost,
							frn.fornecedor as employee,
							max(frn.nome) as employee_name,
                            max(tea.descricao) as team
                    from    tdu_horarios as hor, fornecedores frn, tipoterceiros tea
                    where   frn.fornecedor = hor.cdu_funcionario and
						    hor.cdu_data between @Start and @End and
                            tea.tipoterceiro = frn.tipoterceiro and
                            tea.tipoterceiro = @Team
                    group by frn.fornecedor
                    ) t
            group by employee";

        private const string QueryRewards = @"
            select  max(cdu_premio) as reward 
            from    tdu_escaloes 
            where   cdu_numerohoras <= @TotalHours";

        private readonly Entities _context;
        private readonly SqlConnection _connection;
            
        #endregion

        #region Properties

        public bool ValidLicence
        {
            get
            {
                var licenseKey = ConfigurationManager.AppSettings["licence"];
                return E4RPLicense.License.Validate(E4RPTools.Product.WorkerCommission.ToString(), (long)E4RPTools.Product.WorkerCommission, 1, licenseKey);             
            }
        }

        public ObjectQuery<TDU_Horarios> Timesheet
        {
            get
            {
                new List<TDU_Horarios>(_context.TDU_Horarios);
                return _context.TDU_Horarios;
            }
        }

        public List<TDU_Escaloes> Rewards { get { return new List<TDU_Escaloes>(_context.TDU_Escaloes); } }

        public ObjectQuery<EmployeesView> Employees
        {
            get
            {
                new List<EmployeesView>(_context.EmployeesView);
                return _context.EmployeesView;
            }
        }

        public ObjectQuery<Artigo> Products
        {
            get
            {
                new List<Artigo>(_context.Artigo.Where("it.ArtigoAnulado=false"));
                return _context.Artigo.Where("it.ArtigoAnulado=false");
            }
        }

        public ObjectQuery<TDU_EquivalenciasCBarras> BarCodes
        {
            get
            {
                new List<TDU_EquivalenciasCBarras>(_context.TDU_EquivalenciasCBarras);
                return _context.TDU_EquivalenciasCBarras;
            }
        }

        public List<TeamsView> Teams { get { return new List<TeamsView>(_context.TeamsView); }}

        #endregion

        #region Public Methods

        public Controller()
        {
            _context = new Entities();         
            _connection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
            _connection.Open();
        }

        public void Save()
        {
            _context.SaveChanges();
        }

        public void Save(TDU_Escaloes reward)
        {
            if (reward.CDU_Id_Escalao == 0)
                _context.AddToTDU_Escaloes(reward);
            _context.SaveChanges();
        }

        public void Save(TDU_Horarios timesheet)
        {
            if (timesheet.EntityState == EntityState.Detached)
                _context.AddToTDU_Horarios(timesheet);
            _context.SaveChanges();
        }

        public void Save(TDU_EquivalenciasCBarras barCode)
        {
            barCode.CDU_CodigoBarras = GenerateBarCode(barCode.CDU_Artigo, barCode.CDU_CodigoEntidade);
            if (barCode.CDU_Id_EquivalenciaCBarras == 0)
                _context.AddToTDU_EquivalenciasCBarras(barCode);
            _context.SaveChanges();
        }

        public ObjectQuery<TDU_Horarios> GetTimesheet(DateTime start, DateTime end)
        {
            new List<TDU_Horarios>(_context.TDU_Horarios.Where("it.cdu_data >= @Start and it.cdu_data <= @End", new ObjectParameter("Start", start), new ObjectParameter("End", end)));
            return _context.TDU_Horarios.Where("it.cdu_data >= @Start and it.cdu_data <= @End", new ObjectParameter("Start", start), new ObjectParameter("End", end));
        }

        public ProfitabilityDataSet GetProfitability(DateTime start, DateTime end)
        {
            var profitabilityDataSet = new ProfitabilityDataSet();
            foreach (DataRow teamTotals in GetTeamTotals(start, end))
            {
                var premio = GetReward(Convert.ToDecimal(teamTotals["total_hours"]));
                decimal totalKgsComPremio = 0;
                var rows = new List<ProfitabilityDataSet.RentabilidadeRow>();
                if (teamTotals["team"] == null)
                    continue;
                foreach (DataRow employee in GetTeamEmployees((string)teamTotals["team"], start, end))
                {
                    var equipa = (string) employee["team"];
                    var nome = (string) employee["employee_name"];
                    var kgs = Convert.ToDecimal(employee["KGs"]);
                    var horas = Convert.ToDecimal(employee["hours"]);
                    var custoHora = Convert.ToDecimal(employee["cost"]);
                    var totalHoras = Convert.ToDecimal(teamTotals["total_hours"]);
                    var kgPorHora = horas == 0 ? 0 : kgs / horas;
                    var totalKgs = Convert.ToDecimal(teamTotals["total_kgs"]);
                    var totalRendimento = totalHoras == 0 ? 0 : totalKgs / totalHoras;
                    var valorGlobalPremio = totalHoras * custoHora * premio;
                    var kgsComPremio = (kgPorHora - totalRendimento) * horas;
                    var row = profitabilityDataSet.Rentabilidade.AddRentabilidadeRow(equipa, "", kgs, horas, premio, custoHora, totalHoras, kgPorHora,
                        totalRendimento, valorGlobalPremio, kgsComPremio, 0, 0, 0, start, end, nome);
                    rows.Add(row);
                    totalKgsComPremio += kgsComPremio < 0 ? 0 : kgsComPremio;
                }
                foreach (var row in rows)
                {
                    row.TotalKGsComPremio = totalKgsComPremio;
                    row.PremioPorKG = row.TotalKGsComPremio == 0 ? 0 : row.ValorGlobalPremio / row.TotalKGsComPremio;
                    row.PremioIndividual = row.KGsComPremio * row.PremioPorKG;
                }
            }            
            return profitabilityDataSet;
        }

        public BarCodesDataSet GetBarCodesDataset(List<TDU_EquivalenciasCBarras> barCodes)
        {
            var barCodesDataSet = new BarCodesDataSet();
            foreach (var barCode in barCodes)
            {
                string productName = String.Empty, employeeName = string.Empty;
                var employees = Employees.Where("it.fornecedor=@Employee", new ObjectParameter("Employee", barCode.CDU_CodigoEntidade));
                foreach (var employee in employees)
                    employeeName = employee.Nome;
                var products = Products.Where("it.artigo1=@Product", new ObjectParameter("Product", barCode.CDU_Artigo));
                foreach (var product in products)
                    productName = product.Descricao;
                barCodesDataSet.CodigosBarras.AddCodigosBarrasRow(barCode.CDU_CodigoEntidade, barCode.CDU_Artigo, productName, employeeName, barCode.CDU_CodigoBarras);
            }
            return barCodesDataSet;
        }

        public void AddTeamTimesheet(string team, DateTime day, decimal hours)
        {
            var employees = new List<EmployeesView>(_context.EmployeesView.Where("it.TipoTerceiro=@Team", new ObjectParameter("Team", team)));
            foreach (var employee in employees)
            {
                var found = false;
                var existingTimesheets = _context.TDU_Horarios.Where("it.cdu_data==@Day && it.cdu_funcionario==@Employee", new ObjectParameter("Day", day), 
                    new ObjectParameter("Employee", employee.Fornecedor));
                foreach (var existingTimesheet in existingTimesheets)
                    found = true;
                if (found)
                    continue;
                var timesheet = new TDU_Horarios {CDU_Data = day, CDU_Funcionario = employee.Fornecedor, CDU_Nome = employee.Nome, CDU_NumeroHorasDia = hours };
                Save(timesheet);
            }
        }

        public void AddTeamBarCodes(string team, string product)
        {
            var employees = new List<EmployeesView>(_context.EmployeesView.Where("it.TipoTerceiro=@Team", new ObjectParameter("Team", team)));
            foreach (var employee in employees)
            {
                var found = false;
                var existingBarCodes = _context.TDU_EquivalenciasCBarras.Where("it.cdu_artigo==@Product && it.cdu_codigoentidade==@Employee", new ObjectParameter("Product", product),
                    new ObjectParameter("Employee", employee.Fornecedor));
                foreach (var existingBarCode in existingBarCodes)
                    found = true;
                if (found)
                    continue;
                var barCode = new TDU_EquivalenciasCBarras { CDU_Artigo = product, CDU_CodigoEntidade = employee.Fornecedor};
                Save(barCode);
            }
        }        

        #endregion

        #region Static Private Methods

        private static string GenerateBarCode(string product, string employee)
        {
            if (product.Length > 3 || employee.Length > 6)
                return string.Empty;
            const string country = "560";
            var barcode = country + product.PadLeft(3, '0') + employee.PadLeft(6, '0');
            return barcode + GetCheckSum(barcode);
        }

        private static int GetCheckSum(string barcode)
        {
            var checkSum = 0;
            for (var index = 1; index < barcode.Length; index += 2)
                checkSum += Convert.ToInt32(barcode[index] - '0');
            checkSum *= 3;
            for (var index = 0; index < barcode.Length; index += 2)
                checkSum += Convert.ToInt32(barcode[index] - '0');
            checkSum = Convert.ToInt32(Math.Ceiling(checkSum / 10.0) * 10) - checkSum;
            return checkSum;
        }

        private DataRowCollection GetTeamEmployees(string team, DateTime start, DateTime end)
        {
            var dataSet = new DataSet();
            var dataAdapter = new SqlDataAdapter(QueryTeamEmployees, _connection);
            dataAdapter.SelectCommand.Parameters.AddWithValue("Team", team);
            dataAdapter.SelectCommand.Parameters.AddWithValue("Start", start);
            dataAdapter.SelectCommand.Parameters.AddWithValue("End", end);
            dataAdapter.Fill(dataSet);
            return dataSet.Tables[0].Rows;
        }

        private decimal GetReward(decimal totalHours)
        {
            var dataSet = new DataSet();
            var dataAdapter = new SqlDataAdapter(QueryRewards, _connection);
            dataAdapter.SelectCommand.Parameters.AddWithValue("TotalHours", totalHours);
            dataAdapter.Fill(dataSet);
            if (dataSet.Tables[0].Rows[0].IsNull("reward"))
                return 0;
            return (decimal)dataSet.Tables[0].Rows[0]["reward"];
        }

        private DataRowCollection GetTeamTotals(DateTime start, DateTime end)
        {
            var dataSet = new DataSet();
            var dataAdapter = new SqlDataAdapter(QueryTeamTotals, _connection);
            dataAdapter.SelectCommand.Parameters.AddWithValue("Start", start);
            dataAdapter.SelectCommand.Parameters.AddWithValue("End", end);
            dataAdapter.Fill(dataSet);
            return dataSet.Tables[0].Rows;
        }

        #endregion        
    }
}